package com.zuijiajiao.servlab.databaseWEB.DAOImpl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//import org.apache.tomcat.dbcp.dbcp2.ConnectionFactory;

import com.zuijiajiao.servlab.databaseWEB.DAO.UserDAO;
import com.zuijiajiao.servlab.databaseWEB.DTO.User;
import com.zuijiajiao.servlab.databaseWEB.JDBC.ConnectionFacroty;

public class UserDaoImpl implements UserDAO{

	Connection connection =null;
	PreparedStatement preparedStatemen=null;
	ResultSet resultSet=null;
	@Override
	public void delete(User user) throws ClassNotFoundException {
		try{
			StringBuffer sql=new StringBuffer();
			sql.append("DELETE * FROM tb_user");
			sql.append("WHERE ?=?");
			
			connection=ConnectionFacroty.getConnection();
			preparedStatemen=connection.prepareStatement(sql.toString());
			preparedStatemen.setString(1,"id");
			preparedStatemen.setInt(2, 3);
			
			int result=preparedStatemen.executeUpdate();
			
			if(result !=0){
				System.out.println("成功修改"+result+"条记录");
				
			}else{
				System.out.println("修改失败");
			}
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			ConnectionFacroty.close(connection,preparedStatemen,resultSet);
		}
		
	}

	@Override
	public void update(User user) throws ClassNotFoundException {
		try{
			StringBuffer sql=new StringBuffer();
			sql.append(" UPDATE tb_user");
			sql.append(" SET name=? ");
			sql.append(" SET password = ?");
			sql.append(" WHERE id =?");
			
			connection=ConnectionFacroty.getConnection();
			preparedStatemen=connection.prepareStatement(sql.toString());
			preparedStatemen.setString(1, user.getName());
			preparedStatemen.setString(2, user.getPassword());
			preparedStatemen.setInt(3, user.getUserid());
			
			int result=preparedStatemen.executeUpdate();
			if(result!=0){
				System.out.println("成功修改"+result+"条记录");
			}else{
				System.out.println("0条记录被修改，修改失败");
			}
		}catch (SQLException e) {
			
		}finally{
			ConnectionFacroty.close(connection,preparedStatemen,resultSet);
		}
		
	}

	@Override
	public List<User> findAll(String name, String password) throws ClassNotFoundException {
		try{
			connection=ConnectionFacroty.getConnection();
			
			StringBuffer sql= new StringBuffer();
			sql.append(" SELECT id,name,password ");
			sql.append(" FROM tb_user ");
			sql.append(" WHERE name = ?");
			sql.append(" AND password=?");
			
			preparedStatemen=connection.prepareStatement(sql.toString());
			preparedStatemen.setString(1, name);
			preparedStatemen.setString(2, password);
			resultSet=preparedStatemen.executeQuery();
			
			List<User> users=new ArrayList<User>();
			while(resultSet.next()){
				User user= new User();
				user.setName(resultSet.getString(name));
				user.setPassword(resultSet.getString(password));
				user.setId(resultSet.getInt("id"));
				users.add(user);
				return users;
			}
			return null;
		}catch (SQLException e) {
			 e.printStackTrace();
		}finally{
			ConnectionFacroty.close(connection,preparedStatemen,resultSet);
		}
		return null;
	}

	@Override
	public List<User> findAll() throws ClassNotFoundException {
		try{
			connection = ConnectionFacroty.getConnection();
			
			StringBuffer sql= new StringBuffer();
			sql.append(" SELECT * FROM tb_user");
			
			preparedStatemen=connection.prepareStatement(sql.toString());
			
			resultSet =preparedStatemen.executeQuery();
			List<User> users= new ArrayList<User>();
			while(resultSet.next()){
				User user= new User();
				user.setName(resultSet.getString("name"));
				user.setPassword(resultSet.getString("password"));
				user.setId(resultSet.getInt("id"));
				users.add(user);
				return users;
			}
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			ConnectionFacroty.close(connection,preparedStatemen,resultSet);
			
		}
		return null;
	}

	@Override
	public User findByNameAndPassword(String name, String password) throws ClassNotFoundException {
		
		try{
			StringBuffer sql=new StringBuffer();
			sql.append(" SELECT userid,name,password ");
			sql.append(" FROM tb_user ");
			sql.append(" WHERE name=? ");
			sql.append(" AND password=?");
			connection = ConnectionFacroty.getConnection();
			preparedStatemen=connection.prepareStatement(sql.toString());
			preparedStatemen.setString(1, name);
			preparedStatemen.setString(2, password);
			
			resultSet=preparedStatemen.executeQuery();
			
			User user =new User();
			
			while(resultSet.next())	{
				user.setName(resultSet.getString("name"));
				user.setPassword(resultSet.getString("password"));
				user.setId(resultSet.getInt("userid"));
				return user;
			}
			
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			ConnectionFacroty.close(connection,preparedStatemen,resultSet);
		}
		return null;
	}

	@Override
	public Serializable save(User user) throws ClassNotFoundException {
		try{
			StringBuffer sql = new StringBuffer();
			sql.append(" INSERT INTO tb_user (name,password) ");
			connection = ConnectionFacroty.getConnection();
			preparedStatemen=connection.prepareStatement(sql.toString());
			preparedStatemen.setString(1, user.getName());
			preparedStatemen.setString(2, user.getPassword());
			preparedStatemen.executeUpdate();
			resultSet = preparedStatemen.getGeneratedKeys();
			while(resultSet.next()){
				int id = resultSet.getInt(1);
				System.out.println("id is:"+id);
				return id;
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
		    ConnectionFacroty.close(connection,preparedStatemen,resultSet);
					//ConnectionFacroty.close(connection, preparedStatemen,resultSet);
		}
		return null;
	}

	@Override
	public User get(Serializable id) throws ClassNotFoundException {
		try{
			StringBuffer sql= new StringBuffer();
			sql.append(" SELET id,name,password ");
			sql.append(" FROM tb_user ");
			sql.append(" WHERE id = ? ");
			
			connection=ConnectionFacroty.getConnection();
			preparedStatemen=connection.prepareStatement(sql.toString());
			preparedStatemen.setInt(1, (int)id);
			
			resultSet=preparedStatemen.executeQuery();
			
			User user = new User();
			
			while(resultSet.next()){
				user.setName(resultSet.getString("name"));
				user.setPassword(resultSet.getString("password"));
				user.setId(resultSet.getInt((int)id));
				return user;
				
			}
		}catch(SQLException ex){
				ex.printStackTrace();
			}
		
		return null;
	}

}
